create database Exams
go

use Exams
go

--1. 描述（10分）
--针对如下表Author结构创建索引，写出创建索引的SQL语句：

CREATE TABLE Author 
(
   Id  int  NOT NULL PRIMARY KEY identity,
   FirstName  nvarchar(45) NOT NULL,
   LastName  nvarchar(45) NOT NULL,
   UpdatedTime  datetime NOT NULL
 )

--对FirstName创建唯一索引uniq_idx_firstname，对LastName创建普通索引idx_lastname
create unique index uniq_idx_firstname
on Author(FirstName)
go

create index idx_lastname
on Author(LastName)
go

--构造一个触发器trg_AuditLog，在向Employees表中插入一条数据的时候，触发插入相关的数据到AuditLog中。

-- 职员表
CREATE TABLE Employees
(
   Id INT PRIMARY KEY NOT NULL identity,
   Name nvarchar(80) NOT NULL,
   Age INT NOT NULL,
   Address NVARCHAR(50),
   SALARY decimal(18,2)
);
-- 审计日志表
CREATE TABLE AuditLog
(
   Id int primary key not null identity,
   NAME TEXT NOT NULL,
   Salary decimal(18,2)
);

后台会往Employees插入一条数据:
INSERT INTO Employees (NAME,AGE,ADDRESS,SALARY)VALUES ('Paul', 32, 'California', 20000.00 );
然后从AuditLog里面使用查询语句:
select * from AuditLog;

--输出：
--Paul | 20000.00

go
alter trigger trg_AuditLog
on AuditLog
after
insert
as
begin
	select * from inserted
	insert into AuditLog values('Paul',20000.00)
end
go
INSERT INTO Employees (NAME,AGE,ADDRESS,SALARY)VALUES ('Paul', 32, 'California', 20000.00 );
select * from Employees
select * from AuditLog;


--3. 描述（10分）
--针对Author表创建视图vw_Author，只包含FirstName以及LastName两列，并对这两列重新命名，FirstName为v_FirstName，LastName修改为v_LastName：

CREATE TABLE Author 
(
   Id  int  NOT NULL PRIMARY KEY identity,
   FirstName  nvarchar(45) NOT NULL,
   LastName  nvarchar(45) NOT NULL,
   UpdatedTime  datetime NOT NULL
)
后台会插入2条数据:
insert into Author (FirstName,LastName,UpdatedTime) values ('PENELOPE', 'GUINESS', '2006-02-15 12:34:33'), ('NICK', 'WAHLBERG', '2006-02-15 12:34:33');

--查询视图：select * from vw_Author;
--输出：
--['first_name_v', 'last_name_v']
--PENELOPE|GUINESS
--NICK|WAHLBERG
go
create view vw_Author
as
select 
FirstName as v_FirstName,
LastName as  v_LastName
from Author


create database Exams2
go

use Exams2
go

--4. 描述（10分）
--分页查询Employees表，每5行一页，返回第2页的数据

CREATE TABLE Employees
(
   Id int primary key NOT NULL identity,
   EmployeeCode nvarchar(80),
   Birthday date NOT NULL,
   FirstName nvarchar(14) NOT NULL,
   LastName nvarchar(16) NOT NULL,
   Gender char(1) NOT NULL,
   HireDate date NOT NULL
);
--插入一些数据：
INSERT INTO employees (EmployeeCode,Birthday,FirstName,LastName,Gender,HireDate)  VALUES('10001','1953-09-02','Georgi','Facello','M','1986-06-26');
INSERT INTO employees (EmployeeCode,Birthday,FirstName,LastName,Gender,HireDate)  VALUES('10002','1964-06-02','Bezalel','Simmel','F','1985-11-21');
INSERT INTO employees (EmployeeCode,Birthday,FirstName,LastName,Gender,HireDate)  VALUES('10003','1959-12-03','Parto','Bamford','M','1986-08-28');
INSERT INTO employees (EmployeeCode,Birthday,FirstName,LastName,Gender,HireDate)  VALUES('10004','1954-05-01','Chirstian','Koblick','M','1986-12-01');
INSERT INTO employees (EmployeeCode,Birthday,FirstName,LastName,Gender,HireDate)  VALUES('10005','1955-01-21','Kyoichi','Maliniak','M','1989-09-12');
INSERT INTO employees (EmployeeCode,Birthday,FirstName,LastName,Gender,HireDate)  VALUES('10006','1953-04-20','Anneke','Preusig','F','1989-06-02');
INSERT INTO employees (EmployeeCode,Birthday,FirstName,LastName,Gender,HireDate)  VALUES('10007','1957-05-23','Tzvetan','Zielinski','F','1989-02-10');
INSERT INTO employees (EmployeeCode,Birthday,FirstName,LastName,Gender,HireDate)  VALUES('10008','1958-02-19','Saniya','Kalloufi','M','1994-09-15');
INSERT INTO employees (EmployeeCode,Birthday,FirstName,LastName,Gender,HireDate)  VALUES('10009','1952-04-19','Sumant','Peac','F','1985-02-18');
INSERT INTO employees (EmployeeCode,Birthday,FirstName,LastName,Gender,HireDate)  VALUES('10010','1963-06-01','Duangkaew','Piveteau','F','1989-08-24');
INSERT INTO employees (EmployeeCode,Birthday,FirstName,LastName,Gender,HireDate)  VALUES('10011','1953-11-07','Mary','Sluis','F','1990-01-22');

--查询（5行每页）第2页的记录，输出：
--6|10006|1953-04-20|Anneke|Preusig|F|1989-06-02
--7|10007|1957-05-23|Tzvetan|Zielinski|F|1989-02-10
--8|10008|1958-02-19|Saniya|Kalloufi|M|1994-09-15
--9|10009|1952-04-19|Sumant|Peac|F|1985-02-18
--10|10010|1963-06-01|Duangkaew|Piveteau|F|1989-08-24

select * from Employees
order by Id
offset (2-1)*5 rows fetch next 5 rows only

--第五大题
create database OADatabase
go
use OADatabase
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('PlanningTasks') and o.name = 'FK_PLANNING_REFERENCE_USERAPPL')
alter table PlanningTasks
   drop constraint FK_PLANNING_REFERENCE_USERAPPL
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('UserApplicationInfo') and o.name = 'FK_USERAPPL_REFERENCE_USERINFO')
alter table UserApplicationInfo
   drop constraint FK_USERAPPL_REFERENCE_USERINFO
go

if exists (select 1
            from  sysobjects
           where  id = object_id('DepartmentInfo')
            and   type = 'U')
   drop table DepartmentInfo
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Employees')
            and   type = 'U')
   drop table Employees
go

if exists (select 1
            from  sysobjects
           where  id = object_id('PlanningTasks')
            and   type = 'U')
   drop table PlanningTasks
go

if exists (select 1
            from  sysobjects
           where  id = object_id('UserApplicationInfo')
            and   type = 'U')
   drop table UserApplicationInfo
go

if exists (select 1
            from  sysobjects
           where  id = object_id('UserInfo')
            and   type = 'U')
   drop table UserInfo
go

/*==============================================================*/
/* Table: DepartmentInfo                                        */
/*==============================================================*/
create table DepartmentInfo (
   DId                  int                  not null,
   DName                nvarchar(80)         not null,
   DSupervisor          nvarchar(80)         not null,
   DTelephone           varchar(80)          not null,
   DEmail               varchar(80)          not null,
   constraint PK_DEPARTMENTINFO primary key (DId),
   constraint AK_UQ_NAME_DEPARTME unique (DSupervisor)
)
go

/*==============================================================*/
/* Table: Employees                                             */
/*==============================================================*/
create table Employees (
   EID                  int                  not null,
   EName                nvarchar(80)         not null,
   EPosition            nvarchar(80)         null,
   ETelephone           varchar(80)          not null,
   ESalary              money                not null,
   constraint PK_EMPLOYEES primary key (EID)
)
go

/*==============================================================*/
/* Table: PlanningTasks                                         */
/*==============================================================*/
create table PlanningTasks (
   AID                  int                  null,
   TaskDetail           text                 null,
   PTime                datetime             null
)
go

/*==============================================================*/
/* Table: UserApplicationInfo                                   */
/*==============================================================*/
create table UserApplicationInfo (
   AID                  int                  not null,
   UID                  int                  null,
   AMeetingDate         datetime             null,
   ADay                 varchar(20)          null,
   ANum                 varchar(80)          null,
   AAddress             nvarchar(200)        null,
   ABudget              money                null,
   AAcceptIndex         nvarchar(20)   default('已申请')     null,
   constraint PK_USERAPPLICATIONINFO primary key (AID),
   constraint AK_UQ_AID_USERAPPL unique (AID)
)
go

/*==============================================================*/
/* Table: UserInfo                                              */
/*==============================================================*/
create table UserInfo (
   UID                  int                  not null,
   UName                nvarchar(80)         not null,
   UBankNo              varchar(80)          not null,
   UTelephone           varchar(80)          not null,
   UAddress             nvarchar(200)        not null,
   constraint PK_USERINFO primary key (UID),
   constraint AK_UQ_USERINFO unique (UID)
)
go

alter table PlanningTasks
   add constraint FK_PLANNING_REFERENCE_USERAPPL foreign key (AID)
      references UserApplicationInfo (AID)
go

alter table UserApplicationInfo
   add constraint FK_USERAPPL_REFERENCE_USERINFO foreign key (UID)
      references UserInfo (UID)
go


--题目2（10分）：为了保证当用户申请会议，受理标志为 已申请，请设计一个触发器，
--当用户申请信息表插入数据的时候，可以判断其默认受理标志是否为：已申请，
--如果是，则无需处理允许正常插入，如果不是则重新个性受理标志为：已申请；
create trigger tr_UserApplication
on UserApplicationInfo   
after
insert
as
begin
	declare @accept nvarchar(20)
	if @accept='已申请'
	begin
		select * from inserted
	end
end
go

--题目3（10分）：当业务员受理用户申请的时候，
--需要将当前处理的记录中受理标志修改为：已受理，
--同时将其它所有状态为：已申请，且会议日期已超过的记录中的受理标志个性为：
--已过期，请设计一个存储过程来处理此业务；
create proc proc_Business
@index nvarchar(20)
as
begin
	update UserApplicationInfo set AAcceptIndex=@index 
end
go
